/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ControlEscolar.DAOs;

import com.ControlEscolar.Beans.Student;
import java.util.ArrayList;


/**
 *
 * @author Bere
 */
public class StudentDAO extends AbstractDAO <Student> {

    @Override
    public void add(Student student) throws DAOException {
       String sql = "INSERT INTO students(studentKey,name,firstLastName,secondLastName," +
                "CURP,sex,address,martialStatus," +
                "birthday,bloodType,illness,phone,cel," +
                "email,nationality,birthCert," +
                "DICT_REV,cert,state) values(\""

                +student.getStudentKey()+"\",\""
                + student.getName()+"\",\""
                + student.getFirstLastName()+"\",\""
                + student.getSecondLastName()+"\",\""
                + student.getCURP()+"\",\""
                + student.getSex()+"\",\""
                + student.getAddress()+"\",\""
                + student.getMartialStatus()+"\",\""
                + student.getBirthday()+"\",\""
                + student.getBloodType()+"\",\""
                + student.getIllness()+"\",\""
                + student.getPhone()+"\",\""
                + student.getCel()+"\",\""
                + student.getEmail()+"\",\""
                + student.getNationality()+"\",\""
                + student.getBirthCert()+"\",\""
                + student.getDICT_REV()+"\",\""
                +student.getCert()+"\",\""
                +student.getState()+"\")";


        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }

    }

    @Override
    public int delete(String studentKey) throws DAOException {
        String sql = "DELETE FROM students where studentKey= \""+studentKey+"\"";
        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DAOException();
        }
    return 0;
    }

    @Override
    public int update(Student student) throws DAOException {

        String sql="UPDATE students SET name= \""+student.getName()+"\","+
                "firstLastName= \""+student.getFirstLastName()+"\","+
                "secondLastName= \""+student.getSecondLastName()+"\","+
                "CURP= \""+student.getCURP()+"\","+
                "sex= \""+student.getSex()+"\","+
                "address= \""+student.getAddress()+"\","+
                "martialStatus= \""+student.getMartialStatus()+"\","+
                "birthday= \""+student.getBirthday()+"\","+
                "bloodType= \""+student.getBloodType()+"\","+
                "illness= \""+student.getIllness()+"\","+
                "phone= \""+student.getPhone()+"\","+
                "cel= \""+student.getCel()+"\","+
                "email= \""+student.getEmail()+"\","+
                "nationality= \""+student.getNationality()+"\","+
                "DICT_REV= \""+student.getDICT_REV()+"\","+
                "cert= \""+student.getCert()+"\","+
                "state= \""+student.getState().trim()+"\""+
                " where studentKey = \""+student.getStudentKey()+"\"";

        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
           ex.printStackTrace();
            throw new DAOException();
        }



        return 0;
    }

    @Override
    public ArrayList<Student> query() throws DAOException {
        try {
            String sql = "SELECT * FROM students";
            return connection.executeQuery(sql, Student.class);

        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DAOException();
        }
    }

    public Student queryByKeyCURP(String subjectKey, String CURP) throws DAOException {
        try {
            String sql = "SELECT * FROM students where studentKey = \""+subjectKey+"\" and CURP = \""+CURP+"\"";
            ArrayList<Student> studentQuery=connection.executeQuery(sql, Student.class);
             return getSpecificQuery(studentQuery);

        } catch (Exception ex) {
            throw new DAOException();
        }
    }

     public Student queryByKey(String studentKey) throws DAOException{
         try {
            String sql = "SELECT * FROM students where studentKey = \""+studentKey+"\" ";
            ArrayList<Student> studentQuery=connection.executeQuery(sql, Student.class);
            return getSpecificQuery(studentQuery);

        } catch (Exception ex) {
            throw new DAOException();
        }
     }

     public Student queryDocuments(String studentKey) throws DAOException{
        try {
            String sql = "SELECT * FROM students where studentKey = \"" + studentKey + "\" and birthCert=\"Y\" and cert=\"Y\"";
            ArrayList<Student> studentDocumentsQuery = connection.executeQuery(sql, Student.class);
            return getSpecificQuery(studentDocumentsQuery);
        } catch (Exception ex) {
            throw new DAOException();
        }
     }

     public Student queryActivatedRevalidation(String studentKey) throws DAOException{
          try {
            String sql = "SELECT * FROM students where studentKey = \"" + studentKey + "\" and DICT_REV=\"Y\"";
            ArrayList<Student> studentDocumentsQuery = connection.executeQuery(sql, Student.class);
            return getSpecificQuery(studentDocumentsQuery);
        } catch (Exception ex) {
            throw new DAOException();
        }

     }
     
     public Student queryStudentInactiveState(String studentKey) throws DAOException{
          try {
            String sql = "SELECT * FROM students where studentKey = \"" + studentKey + "\" and state=\"I\"";
            ArrayList<Student> studentDocumentsQuery = connection.executeQuery(sql, Student.class);
            return getSpecificQuery(studentDocumentsQuery);
        } catch (Exception ex) {
            throw new DAOException();
        }

     }


     public void disableState(String studentKey) throws DAOException{
          String sql="UPDATE students SET state=\"I\" where studentKey =\""+studentKey+"\"";
        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }

     }

     public void disableRevalidationStatus(String studentKey) throws DAOException{
         String sql="UPDATE students SET DICT_REV=\"N\" where studentKey =\""+studentKey+"\"";

        try {
            connection.executeUpdate(sql);
        } catch (Exception ex) {
            throw new DAOException();
        }
     }




}

